package com.saic.parse.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.spi.DirStateFactory.Result;

import com.saic.parse.IImport;

public class ImportLowPrice{
	
	private String day = "20140305";
	private String yesterday = "_2014_02_28";
	private double range=1;
	
	public void importExcel(Connection connDataparsedb,Connection connBartack) throws Exception {
		
		String modelId;						// 车型id
		String seriesId = null;				// 车系id
		double price = 0;					// 价格
		double guidingPrice = 0;			// 经销商报价
		String seriesName = null;			// 车系名字
		String maxPrice = null;
		String minPrice = null;
		
		//读取数据   以这次抓取为基准
		ResultSet result = connDataparsedb.createStatement().
			executeQuery("select vel_model_id,min(preferential_price),avg(guiding_price) from t_autohome_vel_model_price" + day + " as price  GROUP BY vel_model_id");
		//读取数据
		while(result.next()){
			modelId = result.getString(1);
			guidingPrice = result.getDouble(3);
			price = result.getDouble(2);
			System.out.println(price);

			double currentRange = (guidingPrice-price)/guidingPrice * 100;				// 算出降幅
			
			if(range <= currentRange){
				
				//查询上一次是否存在
				PreparedStatement statement = connDataparsedb.prepareStatement("select count(*) from t_autohome_vel_model_price"+yesterday+" where vel_model_id=? and preferential_price <= ? ");
				
				statement.setDouble(2, price);
				statement.setString(1,modelId);
				
				ResultSet countResult = statement.executeQuery();
				int count = 0;
				while(countResult.next()){
					 count = countResult.getInt(1);
				}
				countResult.close();
				
				//如果不存在更小的
				if(count == 0){
					ResultSet resultUser = connBartack.createStatement().executeQuery("select id from t_user");
					ResultSet resultModel = connDataparsedb.createStatement().executeQuery("select series_web_id from t_autohome_vel_model where web_id='"+modelId+"'");
					while(resultModel.next()){
						 seriesId = resultModel.getString(1);				// 取出seriesWeb_id
					}
					resultModel.close();
					//取出车系name
					ResultSet resultSeries = connDataparsedb.createStatement().executeQuery("select name from t_autohome_vel_series where web_id='" + seriesId +"'");
					while(resultSeries.next()){
						seriesName = resultSeries.getString(1);
					}
					resultSeries.close();

					//取出价格范围
					ResultSet resultRange = connDataparsedb.createStatement().executeQuery("select series_web_id,min(guiding_price+0),max(guiding_price+0) from t_autohome_vel_model where series_web_id='"+seriesId+"' GROUP BY series_web_id");
					while(resultRange.next()){
						minPrice = resultRange.getString(2);
						maxPrice = resultRange.getString(3);
					}
					resultRange.close();
					//插入降价提醒
					while(resultUser.next()){
						PreparedStatement noticeStatement = connDataparsedb.prepareStatement("insert into t_temp_lowprice_notice(userid,velseriesid,velseriesname,rangeprice)values(?,?,?,?)");
						noticeStatement.setInt(1,resultUser.getInt(1));
						noticeStatement.setString(2,seriesId);
						noticeStatement.setString(3,seriesName);
						noticeStatement.setString(4,minPrice+"~"+maxPrice);
						noticeStatement.execute();
						noticeStatement.close();
					}
				} 
			}
			
		}
	}

	public String getDay() {
		return day;
	}

	public void setDay(String day) {
		this.day = day;
	}

	public String getYesterday() {
		return yesterday;
	}

	public void setYesterday(String yesterday) {
		this.yesterday = yesterday;
	}

	public double getRange() {
		return range;
	}

	public void setRange(double range) {
		this.range = range;
	}
	
	
	
	public static void main(String[] args)throws Exception {
		
		String dataParseSql = "jdbc:mysql://192.168.22.70:3306/dataparser?"
	      + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
		Class.forName("com.mysql.jdbc.Driver");
		Connection connDataParse = DriverManager.getConnection(dataParseSql);
		
		String bartackSql = "jdbc:mysql://192.168.22.70:3306/bartack?"
		      + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
			Class.forName("com.mysql.jdbc.Driver");

		Connection connBartack = DriverManager.getConnection(bartackSql);
		
		ImportLowPrice imp = new ImportLowPrice();
		connDataParse.setAutoCommit(false);
		connBartack.setAutoCommit(false);
		imp.importExcel(connDataParse,connBartack);
		connDataParse.commit();
		connBartack.commit();
		
	}

}
